package com.market.fleamarket.repository;

import com.market.fleamarket.entity.Review;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;
import java.util.Optional;

public interface ReviewRepository extends JpaRepository<Review, Long> {
    // 可扩展自定义查询
    @Query("SELECT AVG((r.descriptionScore + r.responseScore) / 2.0) FROM Review r JOIN r.order o JOIN o.product p WHERE p.user.id = :userId")
    Double findAverageScoreByUserId(@Param("userId") Long userId);

    // 根据商品ID查询买家对商品的评分（使用两个score字段的平均值）
    @Query("SELECT AVG((r.descriptionScore + r.responseScore) / 2.0) FROM Review r JOIN r.order o JOIN o.product p WHERE p.id = :productId")
    Double findAverageResponseScoreByProductId(@Param("productId") Long productId);

    Optional<Review> findByOrder_Id(Long orderId);

    // 根据卖家ID查询评价，完全避免使用"order"作为别名
    @Query("SELECT r FROM Review r JOIN r.order o JOIN o.product p WHERE p.user.id = :sellerId")
    Page<Review> findByOrderProductUserId(@Param("sellerId") Long sellerId, Pageable pageable);

    // 根据评价者ID查询评价
    List<Review> findByReviewerId(Long reviewerId);

    /**
     * 根据卖家ID查询所有评价，用于商品详情页面
     * 此方法不分页，直接返回列表
     */
    @Query("SELECT r FROM Review r JOIN r.order o JOIN o.product p WHERE p.user.id = :sellerId ORDER BY r.createdAt DESC")
    List<Review> findBySellerIdForProductDetail(@Param("sellerId") Long sellerId);

    /**
     * 根据商品ID查询该商品的所有评价
     * 按照评价时间降序排列
     */
    @Query("SELECT r FROM Review r JOIN r.order o JOIN o.product p WHERE p.id = :productId ORDER BY r.createdAt DESC")
    List<Review> findByProductId(@Param("productId") Long productId);
}